﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Caching;
using KL.Store.Core.Domain;
using TN.Data;

namespace KL.Store.Data
{
    public class dbProduct
    {
        #region Get

        public static Product Get(int ProductId, int User)
        {

            var cacheKey = "Product" + ProductId + User;
            if (HttpRuntime.Cache[cacheKey] != null)
            {
                return HttpRuntime.Cache[cacheKey] as Product;
            }

            IList<Product> objectList = new List<Product>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_Get", objectList, new object[] { ProductId, User });
            HttpRuntime.Cache.Add(cacheKey, objectList.FirstOrDefault(), null, DateTime.UtcNow.AddDays(1), Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable, null);
            return objectList.FirstOrDefault();
        }

        #endregion

        #region Get2

        public static Product Get2(int ProductId)
        {
            IList<Product> objectList = new List<Product>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_Get2", objectList, new object[] { ProductId });

            return objectList.FirstOrDefault();
        }

        #endregion

        #region GetList

        public static IList<Product> GetList(int CategoryId, int SubCategories, int Brand, int User, string Search)
        {

            var cacheKey = "ProductGetList" + CategoryId + SubCategories + Brand + User + Search;
            if (HttpRuntime.Cache[cacheKey] != null)
            {
                return HttpRuntime.Cache[cacheKey] as IList<Product>;
            }


            #region Split Search Words

            string search1 = "";
            string search2 = "";
            string search3 = "";
            string search4 = "";
            string search5 = "";

            if (!string.IsNullOrEmpty(Search))
            {
                IList<string> strList = new List<string>();
                while (Search.IndexOf("  ") > 0)
                {
                    Search = Search.Replace("  ", " ");
                }
                Search = Search.Trim();
                strList = Search.Split(' ').ToArray<string>();
                if (strList.Count > 0) search1 = strList[0];
                if (strList.Count > 1) search2 = strList[1];
                if (strList.Count > 2) search3 = strList[2];
                if (strList.Count > 3) search4 = strList[3];
                if (strList.Count > 4) search5 = strList[4];
            }

            #endregion

            IList<Product> objectList = new List<Product>();
            if (CategoryId <= 0 && string.IsNullOrEmpty(search1))
            {
                CategoryId = KL.Store.Core.Parameters.MainCategoryId;
            }
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_List", objectList, new object[] { CategoryId, SubCategories, Brand, User, search1, search2, search3, search4, search5 });
            HttpRuntime.Cache.Add(cacheKey, objectList, null, DateTime.UtcNow.AddDays(1), Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable, null);
            return objectList;
        }

        #endregion

        #region GetListFull

        public static IList<Product> GetListFull(int CategoryId, int SubCategories, int Brand, int User, string Search)
        {
            #region Split Search Words

            string search1 = "";
            string search2 = "";
            string search3 = "";
            string search4 = "";
            string search5 = "";

            if (!string.IsNullOrEmpty(Search))
            {
                IList<string> strList = new List<string>();
                while (Search.IndexOf("  ") > 0)
                {
                    Search = Search.Replace("  ", " ");
                }
                Search = Search.Trim();
                strList = Search.Split(' ').ToArray<string>();
                if (strList.Count > 0) search1 = strList[0];
                if (strList.Count > 1) search2 = strList[1];
                if (strList.Count > 2) search3 = strList[2];
                if (strList.Count > 3) search4 = strList[3];
                if (strList.Count > 4) search5 = strList[4];
            }

            #endregion

            IList<Product> objectList = new List<Product>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_List_Full", objectList, new object[] { CategoryId, SubCategories, Brand, User, search1, search2, search3, search4, search5 });

            return objectList;
        }

        #endregion

        #region GetListFullWithFilter

        public static IList<Product> GetListFullWithFilter(int CategoryId, int BrandId, int PageId, int User, string FreeCargo, string FastCargo, string NewProduct, string OpportunityProduct, string CampaignProduct, string ShowAsCampaignProduct, string GiftString, string SpecialText, string CheckStock)
        {
            IList<Product> objectList = new List<Product>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_List_Full_WithFilters", objectList, new object[] { CategoryId, BrandId, PageId, User, FreeCargo, FastCargo, NewProduct, OpportunityProduct, CampaignProduct, ShowAsCampaignProduct, GiftString, SpecialText, CheckStock });

            return objectList;
        }

        #endregion

        #region GetListFullOnlyMain

        public static IList<Product> GetListFullOnlyMain(int CategoryId, int SubCategories, int Brand, int User, string Search)
        {
            #region Split Search Words

            string search1 = "";
            string search2 = "";
            string search3 = "";
            string search4 = "";
            string search5 = "";

            if (!string.IsNullOrEmpty(Search))
            {
                IList<string> strList = new List<string>();
                while (Search.IndexOf("  ") > 0)
                {
                    Search = Search.Replace("  ", " ");
                }
                Search = Search.Trim();
                strList = Search.Split(' ').ToArray<string>();
                if (strList.Count > 0) search1 = strList[0];
                if (strList.Count > 1) search2 = strList[1];
                if (strList.Count > 2) search3 = strList[2];
                if (strList.Count > 3) search4 = strList[3];
                if (strList.Count > 4) search5 = strList[4];
            }

            #endregion

            IList<Product> objectList = new List<Product>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_List_Full_Only_Main_Cat", objectList, new object[] { CategoryId, SubCategories, Brand, User, search1, search2, search3, search4, search5 });

            return objectList;
        }

        #endregion

        #region GetBestSellerList

        public static IList<Product> GetBestSellerList(int CategoryId, int SubCategories, int Brand, int User, string Search, int totalItemCount, string orderByString)
        {
            #region Split Search Words

            string search1 = "";
            string search2 = "";
            string search3 = "";
            string search4 = "";
            string search5 = "";

            if (!string.IsNullOrEmpty(Search))
            {
                IList<string> strList = new List<string>();
                while (Search.IndexOf("  ") > 0)
                {
                    Search = Search.Replace("  ", " ");
                }
                Search = Search.Trim();
                strList = Search.Split(' ').ToArray<string>();
                if (strList.Count > 0) search1 = strList[0];
                if (strList.Count > 1) search2 = strList[1];
                if (strList.Count > 2) search3 = strList[2];
                if (strList.Count > 3) search4 = strList[3];
                if (strList.Count > 4) search5 = strList[4];
            }

            #endregion

            IList<Product> objectList = new List<Product>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_List_BestSeller", objectList, new object[] { CategoryId, SubCategories, Brand, User, search1, search2, search3, search4, search5, totalItemCount, orderByString });

            return objectList;
        }

        #endregion

        #region GetBestSellerList2

        public static IList<Product> GetBestSellerList2(int CategoryId, int SubCategories, int Brand, int User, string Search, int totalItemCount, string orderByString)
        {
            #region Split Search Words

            string search1 = "";
            string search2 = "";
            string search3 = "";
            string search4 = "";
            string search5 = "";

            if (!string.IsNullOrEmpty(Search))
            {
                IList<string> strList = new List<string>();
                while (Search.IndexOf("  ") > 0)
                {
                    Search = Search.Replace("  ", " ");
                }
                Search = Search.Trim();
                strList = Search.Split(' ').ToArray<string>();
                if (strList.Count > 0) search1 = strList[0];
                if (strList.Count > 1) search2 = strList[1];
                if (strList.Count > 2) search3 = strList[2];
                if (strList.Count > 3) search4 = strList[3];
                if (strList.Count > 4) search5 = strList[4];
            }

            #endregion

            IList<Product> objectList = new List<Product>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_List_BestSeller2", objectList, new object[] { CategoryId, SubCategories, Brand, User, search1, search2, search3, search4, search5, totalItemCount, orderByString });

            return objectList;
        }

        #endregion

        #region GetCampaignsList

        public static IList<Product> GetCampaignsList(int CategoryId, int SubCategories, int Brand, int User, string Search, int totalItemCount, string orderByString)
        {
            #region Split Search Words

            string search1 = "";
            string search2 = "";
            string search3 = "";
            string search4 = "";
            string search5 = "";

            if (!string.IsNullOrEmpty(Search))
            {
                IList<string> strList = new List<string>();
                while (Search.IndexOf("  ") > 0)
                {
                    Search = Search.Replace("  ", " ");
                }
                Search = Search.Trim();
                strList = Search.Split(' ').ToArray<string>();
                if (strList.Count > 0) search1 = strList[0];
                if (strList.Count > 1) search2 = strList[1];
                if (strList.Count > 2) search3 = strList[2];
                if (strList.Count > 3) search4 = strList[3];
                if (strList.Count > 4) search5 = strList[4];
            }

            #endregion

            IList<Product> objectList = new List<Product>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_List_Campaigns", objectList, new object[] { CategoryId, SubCategories, Brand, User, search1, search2, search3, search4, search5, totalItemCount, orderByString });

            return objectList;
        }

        #endregion

        #region GetCampaignsListByType

        public static IList<Product> GetCampaignsListByType(int CategoryId, int SubCategories, int Brand, int User, string Type)
        {

            IList<Product> objectList = new List<Product>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_List_Campaigns_Single_Type", objectList, new object[] { CategoryId, SubCategories, Brand, User, Type });

            return objectList;
        }

        #endregion

        #region GetListCat

        public static IList<Product> GetListCat(int CategoryId, int itemCount, int User)
        {

            IList<Product> objectList = new List<Product>();
            SqlHelper.FillList(dbParameters.dsnStore, "SR_STOK_GRUP_LISTESI_FOR_LIST_IN_CATS", objectList, new object[] { CategoryId, 1, 0, itemCount, User });

            return objectList;
        }

        #endregion

        #region GetListCatFull

        public static IList<Product> GetListCatFull(int CategoryId, int itemCount, int User)
        {

            IList<Product> objectList = new List<Product>();
            SqlHelper.FillList(dbParameters.dsnStore, "SR_STOK_GRUP_LISTESI_FOR_LIST_IN_CATS_FULL", objectList, new object[] { CategoryId, 1, 0, itemCount, User });

            return objectList;
        }

        #endregion

        #region GetListForExport

        public static IList<Product> GetListForExport(int CategoryId, short AddedType, short IsActive)
        {

            IList<Product> objectList = new List<Product>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_List_Export", objectList, new object[] { CategoryId, AddedType, IsActive });

            return objectList;
        }

        #endregion

        #region GetImageList

        public static IList<ProductImage> GetImageList(int ProductId)
        {
            IList<ProductImage> objectList = new List<ProductImage>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Image_List", objectList, new object[] { ProductId });

            return objectList;
        }

        #endregion

        #region GetImageListWithoutVariant

        public static IList<ProductImage> GetImageListWithoutVariant(int ProductId)
        {
            IList<ProductImage> objectList = new List<ProductImage>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Image_List_Without_Variant", objectList, new object[] { ProductId });

            return objectList;
        }

        #endregion

        #region GetVariantCombo

        public static IList<variantProperties> GetVariantCombo(int ProductId)
        {
            IList<variantProperties> objectList = new List<variantProperties>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_Variant_Combo", objectList, new object[] { ProductId });

            return objectList;
        }

        #endregion

        #region GetVariantUrls

        public static IList<variantUrls> GetVariantUrls(int ProductId)
        {
            IList<variantUrls> objectList = new List<variantUrls>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_Variant_Url_List", objectList, new object[] { ProductId });

            return objectList;
        }

        #endregion

        #region GetAllImages

        public static IList<ProductImage> GetAllImages()
        {
            IList<ProductImage> objectList = new List<ProductImage>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Image_ListAll", objectList);

            return objectList;
        }

        #endregion

        #region GetDescription

        public static string GetDescription(int ProductId)
        {
            string desc = (string)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Get_Description", new object[] { ProductId });
            return desc;
        }
        #endregion

        #region InsertImage

        public static void InsertImage(int ProductId, short ImageId)
        {
            SqlHelper.ExecuteNonQuery(dbParameters.dsnStore, "SPR_ESTORE_Image_Insert", new object[] { ProductId, ImageId });
        }

        #endregion

        #region DeleteImage

        public static void DeleteImage(int ProductId, short ImageId)
        {
            SqlHelper.ExecuteNonQuery(dbParameters.dsnStore, "SPR_ESTORE_Image_Delete", new object[] { ProductId, ImageId });
        }

        #endregion

        #region GetLiveSearch

        public static IList<ProductLiveSearch> GetLiveSearch(string Search)
        {
            string search1 = "";
            string search2 = "";
            string search3 = "";
            string search4 = "";
            string search5 = "";

            if (!string.IsNullOrEmpty(Search))
            {
                IList<string> strList = new List<string>();
                Search = Search.Replace("\"", " ").Replace("'", " ");
                while (Search.IndexOf("  ") > -1)
                {
                    Search = Search.Replace("  ", " ");
                }
                Search = Search.Trim();
                strList = Search.Split(' ').ToArray<string>();
                if (strList.Count > 0) search1 = strList[0];
                if (strList.Count > 1) search2 = strList[1];
                if (strList.Count > 2) search3 = strList[2];
                if (strList.Count > 3) search4 = strList[3];
                if (strList.Count > 4) search5 = strList[4];
            }
            IList<ProductLiveSearch> objectList = new List<ProductLiveSearch>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_Live_Search", objectList, new object[] { search1, search2, search3, search4, search5 });

            return objectList;
        }

        #endregion

        #region GetLiveSearchFull

        public static IList<ProductLiveSearch> GetLiveSearchFull(string Search)
        {
            string search1 = "";
            string search2 = "";
            string search3 = "";
            string search4 = "";
            string search5 = "";

            if (!string.IsNullOrEmpty(Search))
            {
                IList<string> strList = new List<string>();
                Search = Search.Replace("\"", " ").Replace("'", " ");
                while (Search.IndexOf("  ") > -1)
                {
                    Search = Search.Replace("  ", " ");
                }
                Search = Search.Trim();
                strList = Search.Split(' ').ToArray<string>();
                if (strList.Count > 0) search1 = strList[0];
                if (strList.Count > 1) search2 = strList[1];
                if (strList.Count > 2) search3 = strList[2];
                if (strList.Count > 3) search4 = strList[3];
                if (strList.Count > 4) search5 = strList[4];
            }
            IList<ProductLiveSearch> objectList = new List<ProductLiveSearch>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_Live_Search_Full", objectList, new object[] { search1, search2, search3, search4, search5 });

            return objectList;
        }

        #endregion

        #region GetMeta

        public static ProductMeta GetMeta(int ProductId)
        {
            var cacheKey = "ProductGetMeta" + ProductId;
            if (HttpRuntime.Cache[cacheKey] != null)
            {
                return HttpRuntime.Cache[cacheKey] as ProductMeta;
            }
            
            IList<ProductMeta> objectList = new List<ProductMeta>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_Get_Meta", objectList, new object[] { ProductId });
            HttpRuntime.Cache.Add(cacheKey, objectList.FirstOrDefault(), null, DateTime.UtcNow.AddDays(1), Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable, null);
            return objectList.FirstOrDefault();
        }

        #endregion

        #region GetBrandList

        public static IList<Brand> GetBrandList(int CategoryId, int SubCategories, string Search)
        {
            IList<Brand> objectList = new List<Brand>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_Brand_List", objectList, new object[] { CategoryId, SubCategories, Search });

            return objectList;
        }

        #endregion

        #region GetPropertiesList

        public static IList<Property> GetPropertiesList(int CategoryId)
        {
            IList<Property> objectList = new List<Property>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Category_Property_List", objectList, new object[] { CategoryId });

            return objectList;
        }

        #endregion

        #region GetPropertyList

        public static IList<PropertyList> GetPropertyList(string Aciklama, int Kategori)
        {
            IList<PropertyList> objectList = new List<PropertyList>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Category_Properties_List", objectList, new object[] { Aciklama, Kategori });

            return objectList;
        }

        #endregion

        #region GetProductIdsWithPropertyString

        public static IList<int> GetProductIdsWithPropertyString(string propertyString)
        {
            IList<int> objectList = new List<int>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Category_Properties_Get_ProductIds", objectList, new object[] { propertyString });

            return objectList;
        }

        #endregion

        #region GetProductBrandListSingle

        //public static string GetProductBrandListSingle(int ProductId)
        //{
        //    IList<BrandSingle> objectList = new List<BrandSingle>();
        //    SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_Brand_List_Single", objectList, new object[] { ProductId });
        //    var allObjects = objectList.ToList<BrandSingle>();
        //    return Convert.ToString(allObjects.Select(o => o.BrandList));
        //}

        #endregion

        #region GetByCode

        public static int GetByCode(string ProductCode)
        {
            int? productId;
            productId = (int?)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_GetByCode", new object[] { ProductCode });
            if (productId.HasValue)
            {
                return (int)productId;
            }
            else
            {
                return -1;
            }
        }

        #endregion

        #region GetByXMLProductId

        public static int GetByXMLProductId(short XMLId, string ProductId)
        {
            // -1 ürün yok
            // -2 stok güncellenmeyecek
            // >0 stok id
            int? productId;
            productId = (int?)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_GetByXMLCode", new object[] { XMLId, ProductId });
            if (productId.HasValue)
            {
                return (int)productId;
            }
            else
            {
                return -1;
            }
        }

        #endregion

        #region GetByXMLProductId_2

        public static int GetByXMLProductId_2(short XMLId, string ProductId)
        {
            // -1 ürün yok
            // >0 stok id
            int? productId;
            productId = (int?)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_GetByXMLCode2", new object[] { XMLId, ProductId });
            if (productId.HasValue)
            {
                return (int)productId;
            }
            else
            {
                return -1;
            }
        }

        #endregion

        #region GetProductIdByProductName

        public static int GetProductIdByProductName(string ProductName)
        {
            int? productId;
            productId = (int?)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_ProductId_GetByProductName", new object[] { ProductName });
            if (productId.HasValue)
            {
                return (int)productId;
            }
            else
            {
                return -1;
            }
        }

        #endregion

        #region GetProfitRateByProductId

        public static ProductProfitRate GetProfitRateByProductId(int ProductId)
        {
            IList<ProductProfitRate> profitRate = new List<ProductProfitRate>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_GetProfitRate_ByProductId", profitRate, new object[] { ProductId });
            return profitRate.FirstOrDefault();
        }

        #endregion

        #region Insert

        public static int Insert(short XMLId, Product p, bool ReloadDescription)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Insert",
                                                  new object[] { XMLId, p.ProductCode, p.ProductName, p.CategoryId, p.BrandId, p.TaxRate, p.Description, ReloadDescription, p.Desi, p.DeliveryPeriod, p.DeliveryTime, p.xmlBrandId, p.xmlGroupId });

            return id;
        }

        #endregion

        #region InsertWithoutName

        public static int InsertWithoutName(short XMLId, Product p, bool ReloadDescription)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Insert_Without_Name",
                                                  new object[] { XMLId, p.ProductCode, p.CategoryId, p.BrandId, p.TaxRate, p.Description, ReloadDescription, p.Desi, p.DeliveryPeriod, p.DeliveryTime, p.xmlBrandId, p.xmlGroupId });

            return id;
        }

        #endregion

        #region UpdateGGProductId

        public static void UpdateGGProductId(int ProductId, int GGProductId)
        {
            SqlHelper.ExecuteNonQuery(dbParameters.dsnStore, "SPR_ESTORE_Product_UpdateGGId", new object[] { ProductId, GGProductId });
        }

        #endregion

        #region Insert2

        public static int Insert2(short XMLId, string ProductId, string KartKod, string Barcode, string ProductName,
                                  int CategoryId, int BrandId, decimal TaxRate, string Birim, decimal Price, decimal BuyingPrice, string Currency, string Notlar, bool ReloadDescription,
                                  bool OmurBoyuGaranti, short GarantiSure, string GarantiDonem, string TeslimDonem, short TeslimSure,
                                  decimal En, decimal Boy, decimal Yukseklik, decimal Agirlik, decimal Desi,
                                  bool HizliKargo, bool UcretsizKargo, bool YeniUrun, decimal KargoUcret, decimal EkKargoUcret,
                                  bool Aktif, string Model, decimal StokMiktar, decimal StokMiktarYolda)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Insert2", new object[]
                                                 {XMLId, ProductId, KartKod, Barcode, ProductName,
                                                  CategoryId, BrandId, TaxRate, Birim, Price, BuyingPrice, Currency, Notlar, ReloadDescription,
                                                  OmurBoyuGaranti, GarantiSure, GarantiDonem, TeslimDonem, TeslimSure,
                                                  En,  Boy, Yukseklik, Agirlik, Desi,
                                                  HizliKargo, UcretsizKargo, YeniUrun, KargoUcret, EkKargoUcret,
                                                  Aktif, Model, StokMiktar, StokMiktarYolda });

            return id;
        }

        #endregion

        #region Insert3

        public static int Insert3(short XMLId, string ProductId, string KartKod, string Barcode, string ProductName,
                                  int CategoryId, int BrandId, decimal TaxRate, string Birim, decimal Price, decimal BuyingPrice, string Currency, string Notlar, bool ReloadDescription,
                                  bool OmurBoyuGaranti, short GarantiSure, string GarantiDonem, string TeslimDonem, short TeslimSure,
                                  decimal En, decimal Boy, decimal Yukseklik, decimal Agirlik, decimal Desi,
                                  bool HizliKargo, bool UcretsizKargo, bool YeniUrun, decimal KargoUcret, decimal EkKargoUcret,
                                  bool Aktif, string Model, decimal StokMiktar, decimal StokMiktarYolda, string VaryantId, string VaryantValue)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Insert3", new object[]
                                                 {XMLId, ProductId, KartKod, Barcode, ProductName,
                                                  CategoryId, BrandId, TaxRate, Birim, Price, BuyingPrice, Currency, Notlar, ReloadDescription,
                                                  OmurBoyuGaranti, GarantiSure, GarantiDonem, TeslimDonem, TeslimSure,
                                                  En,  Boy, Yukseklik, Agirlik, Desi,
                                                  HizliKargo, UcretsizKargo, YeniUrun, KargoUcret, EkKargoUcret,
                                                  Aktif, Model, StokMiktar, StokMiktarYolda, VaryantId, VaryantValue });

            return id;
        }

        #endregion

        #region Insert4

        public static int Insert4(short XMLId, string ProductId, string KartKod, string Barcode, string ProductName,
                                  int CategoryId, int BrandId, decimal TaxRate, string Birim, decimal Price, decimal BuyingPrice, string Currency, string Notlar, bool ReloadDescription,
                                  bool OmurBoyuGaranti, short GarantiSure, string GarantiDonem, string TeslimDonem, short TeslimSure,
                                  decimal En, decimal Boy, decimal Yukseklik, decimal Agirlik, decimal Desi,
                                  bool HizliKargo, bool UcretsizKargo, bool YeniUrun, bool FirsatUrun, decimal KargoUcret, decimal EkKargoUcret,
                                  bool Aktif, string Model, decimal StokMiktar, decimal StokMiktarYolda, decimal IndirimliGosterim)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Insert4", new object[]
                                                 {XMLId, ProductId, KartKod, Barcode, ProductName,
                                                  CategoryId, BrandId, TaxRate, Birim, Price, BuyingPrice, Currency, Notlar, ReloadDescription,
                                                  OmurBoyuGaranti, GarantiSure, GarantiDonem, TeslimDonem, TeslimSure,
                                                  En,  Boy, Yukseklik, Agirlik, Desi,
                                                  HizliKargo, UcretsizKargo, YeniUrun, FirsatUrun, KargoUcret, EkKargoUcret,
                                                  Aktif, Model, StokMiktar, StokMiktarYolda, IndirimliGosterim });

            return id;
        }

        #endregion

        #region insertStokKartSaticiByName

        public static int insertStokKartSaticiByName(int KRTID, int XMLID, string XMLKARTID, string XMLKARTKOD, string XMLKARTBARKOD, decimal ALISFIYATI, decimal STOKMIKTAR, decimal STOKMIKTARYOLDA, int GUNCELLE)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_StokKartSaticiInsert", new object[]
                                                 {KRTID, XMLID, XMLKARTID, XMLKARTKOD,
                                                  XMLKARTBARKOD, ALISFIYATI, STOKMIKTAR, STOKMIKTARYOLDA, GUNCELLE });

            return id;
        }

        #endregion

        #region InsertWithoutProductName

        public static int InsertWithoutProductName(short XMLId, string ProductId, string KartKod, string Barcode,
                                  int CategoryId, int BrandId, decimal TaxRate, string Birim, decimal Price, decimal BuyingPrice, string Currency, string Notlar, bool ReloadDescription,
                                  bool OmurBoyuGaranti, short GarantiSure, string GarantiDonem, string TeslimDonem, short TeslimSure,
                                  decimal En, decimal Boy, decimal Yukseklik, decimal Agirlik, decimal Desi,
                                  bool HizliKargo, bool UcretsizKargo, bool YeniUrun, decimal KargoUcret, decimal EkKargoUcret,
                                  bool Aktif, string Model, decimal StokMiktar, decimal StokMiktarYolda)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Insert_Without_ProductName", new object[]
                                                 {XMLId, ProductId, KartKod, Barcode,
                                                  CategoryId, BrandId, TaxRate, Birim, Price, BuyingPrice, Currency, Notlar, ReloadDescription,
                                                  OmurBoyuGaranti, GarantiSure, GarantiDonem, TeslimDonem, TeslimSure,
                                                  En,  Boy, Yukseklik, Agirlik, Desi,
                                                  HizliKargo, UcretsizKargo, YeniUrun, KargoUcret, EkKargoUcret,
                                                  Aktif, Model, StokMiktar, StokMiktarYolda });

            return id;
        }

        #endregion

        #region InsertWithoutProductName4

        public static int InsertWithoutProductName4(short XMLId, string ProductId, string KartKod, string Barcode,
                                  int CategoryId, int BrandId, decimal TaxRate, string Birim, decimal Price, decimal BuyingPrice, string Currency, string Notlar, bool ReloadDescription,
                                  bool OmurBoyuGaranti, short GarantiSure, string GarantiDonem, string TeslimDonem, short TeslimSure,
                                  decimal En, decimal Boy, decimal Yukseklik, decimal Agirlik, decimal Desi,
                                  bool HizliKargo, bool UcretsizKargo, bool YeniUrun, bool FirsatUrun, decimal KargoUcret, decimal EkKargoUcret,
                                  bool Aktif, string Model, decimal StokMiktar, decimal StokMiktarYolda, decimal IndirimliGosterim)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Insert_Without_ProductName4", new object[]
                                                 {XMLId, ProductId, KartKod, Barcode,
                                                  CategoryId, BrandId, TaxRate, Birim, Price, BuyingPrice, Currency, Notlar, ReloadDescription,
                                                  OmurBoyuGaranti, GarantiSure, GarantiDonem, TeslimDonem, TeslimSure,
                                                  En,  Boy, Yukseklik, Agirlik, Desi,
                                                  HizliKargo, UcretsizKargo, YeniUrun, FirsatUrun, KargoUcret, EkKargoUcret,
                                                  Aktif, Model, StokMiktar, StokMiktarYolda, IndirimliGosterim });

            return id;
        }

        #endregion

        #region InsertWithoutProductNameAndVariant

        public static int InsertWithoutProductNameAndVariant(short XMLId, string ProductId, string KartKod, string Barcode,
                                  int CategoryId, int BrandId, decimal TaxRate, string Birim, decimal Price, decimal BuyingPrice, string Currency, string Notlar, bool ReloadDescription,
                                  bool OmurBoyuGaranti, short GarantiSure, string GarantiDonem, string TeslimDonem, short TeslimSure,
                                  decimal En, decimal Boy, decimal Yukseklik, decimal Agirlik, decimal Desi,
                                  bool HizliKargo, bool UcretsizKargo, bool YeniUrun, decimal KargoUcret, decimal EkKargoUcret,
                                  bool Aktif, string Model, decimal StokMiktar, decimal StokMiktarYolda, string VariantId, string VaryantValue)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Insert_Without_ProductName_Variant", new object[]
                                                 {XMLId, ProductId, KartKod, Barcode,
                                                  CategoryId, BrandId, TaxRate, Birim, Price, BuyingPrice, Currency, Notlar, ReloadDescription,
                                                  OmurBoyuGaranti, GarantiSure, GarantiDonem, TeslimDonem, TeslimSure,
                                                  En,  Boy, Yukseklik, Agirlik, Desi,
                                                  HizliKargo, UcretsizKargo, YeniUrun, KargoUcret, EkKargoUcret,
                                                  Aktif, Model, StokMiktar, StokMiktarYolda, VariantId, VaryantValue });

            return id;
        }

        #endregion

        #region GetVariantId

        public static int GetVariantId(int ProductId)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Get_Variant_Id", new object[] { ProductId });

            return id;
        }

        #endregion

        #region ResetAmount

        public static void ResetAmount(short XmlId)
        {
            SqlHelper.ExecuteNonQuery(dbParameters.dsnStore, "SPR_ESTORE_Product_ResetAmount", new object[] { XmlId });
        }

        #endregion

        #region SetInactive

        public static void SetInactive(short XmlId)
        {
            SqlHelper.ExecuteNonQuery(dbParameters.dsnStore, "SPR_ESTORE_Product_SetInactive", new object[] { XmlId });
        }

        #endregion

        #region SetInactiveByProductId

        public static void SetInactiveByProductId(int productId)
        {
            SqlHelper.ExecuteNonQuery(dbParameters.dsnStore, "SPR_ESTORE_Product_SetInactiveByProductId", new object[] { productId });
        }

        #endregion

        #region SetActiveByProductId

        public static void SetActiveByProductId(int productId)
        {
            SqlHelper.ExecuteNonQuery(dbParameters.dsnStore, "SPR_ESTORE_Product_SetActiveByProductId", new object[] { productId });
        }

        #endregion

        #region InsertGroup

        public static void InsertGroup(int ProductId, int CategoryId, string Type)
        {
            SqlHelper.ExecuteNonQuery(dbParameters.dsnStore, "SPR_ESTORE_Product_Insert_Group", new object[] { ProductId, CategoryId, Type });
        }

        #endregion

        #region GetGroupList

        public static IList<ProductGroup> GetGroupList(int GroupId)
        {
            IList<ProductGroup> objectList = new List<ProductGroup>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_ProductGroup_List", objectList, new object[] { GroupId });

            return objectList;
        }

        #endregion

        #region GetGroupListFull

        public static IList<ProductGroup> GetGroupListFull(string GroupType)
        {
            IList<ProductGroup> objectList = new List<ProductGroup>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_ProductGroup_List2", objectList, new object[] { GroupType });

            return objectList;
        }

        #endregion

        #region Xml Methods Update Fields

        #region UpdateAmount

        public static void UpdateAmount(int ProductId, short XMLId, bool FastCargo, decimal Amount, decimal AmountOnRoad)
        {
            SqlHelper.ExecuteNonQuery(dbParameters.dsnStore, "SPR_ESTORE_Product_UpdateAmount", new object[] { ProductId, XMLId, FastCargo, Amount, AmountOnRoad });
        }

        #endregion

        #region UpdatePrice

        public static void UpdatePrice(short XmlId, int ProductId, decimal Price, decimal BuyingPrice, string Currency)
        {
            SqlHelper.ExecuteNonQuery(dbParameters.dsnStore, "SPR_ESTORE_Product_UpdatePrice", new object[] { XmlId, ProductId, Price, BuyingPrice, Currency });
        }

        #endregion

        #region UpdateDesi

        public static void UpdateDesi(int ProductId, decimal Desi)
        {
            SqlHelper.ExecuteNonQuery(dbParameters.dsnStore, "SPR_ESTORE_Product_UpdateDesi", new object[] { ProductId, Desi });
        }

        #endregion

        #region UpdateFreeCargo

        public static void UpdateFreeCargo(int ProductId, bool FreeCargo)
        {
            SqlHelper.ExecuteNonQuery(dbParameters.dsnStore, "SPR_ESTORE_Product_UpdateFreeCargo", new object[] { ProductId, FreeCargo });
        }

        #endregion

        #region UpdateDescription

        public static void UpdateDescription(int ProductId, string Description)
        {
            SqlHelper.ExecuteNonQuery(dbParameters.dsnStore, "SPR_ESTORE_Product_UpdateDescription", new object[] { ProductId, Description });
        }

        #endregion

        #region UpdateNebimXmlProducts

        public static void UpdateNebimXmlProducts(string ProductCode, short XMLId, decimal Tax, decimal Price, decimal AmountOnRoad)
        {
            SqlHelper.ExecuteNonQuery(dbParameters.dsnStore, "SPR_ESTORE_Product_Xml_Nebim_Update", new object[] { ProductCode, XMLId, Tax, Price, AmountOnRoad });
        }

        #endregion

        #endregion

        #region UpdateProductCode

        public static int UpdateProductCode(int ProductId)
        {
            return (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Update_Code", new object[] { ProductId });
        }
        #endregion

        #region ResetProductCode

        public static int ResetProductCode(int ProductId, string ProductCode)
        {
            return (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Reset_Code", new object[] { ProductId, ProductCode });
        }
        #endregion

        #region AddNewProduct

        public static int AddNewProduct(Product product, string StokTut, int CategoryId, int SelectedBrand)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_AddNewProduct", new object[]
                                                 {product.IsActive, product.UstUrunId, product.ProductName, product.ProductName2, product.Model, product.Description,
                                                  product.Price, product.TaxRate, product.NewProduct,  product.FastCargo, product.FreeCargo,
                                                  product.OpportunityProduct, product.CampaignProduct, product.ShowAsCampaignProduct, product.DiscountTotal, product.DiscountPercent, product.DiscountDisplayTotal,
                                                  product.Width, product.Length, product.Height, product.Desi, product.Weight, product.GiftString, product.SpecialText, product.InstallmentNeeded, product.InstallmentPrice,
                                                  product.CargoPrice, product.AddtCargoPrice, product.DeliveryTime, product.DeliveryPeriod, product.VarrantyTime,
                                                  product.VarrantyPeriod, product.FreeCargoInCity, product.FreeInstallmentInCity, StokTut, product.Transport, CategoryId, SelectedBrand });
            return id;
        }

        #endregion

        #region SaveProduct

        public static int SaveProduct(Product product, string StokTut)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_SaveProduct", new object[]
                                                 {product.ProductId, product.IsActive, product.UstUrunId, product.ProductName, product.ProductName2, product.Model, product.Description,
                                                  product.Price, product.DealerSellingPrice, product.TaxRate, product.NewProduct,  product.FastCargo, product.FreeCargo,
                                                  product.OpportunityProduct, product.CampaignProduct, product.ShowAsCampaignProduct, product.DiscountTotal, product.DiscountPercent, product.DiscountDisplayTotal,
                                                  product.Width, product.Length, product.Height, product.Desi, product.Weight, product.GiftString, product.SpecialText, product.InstallmentNeeded, product.InstallmentPrice,
                                                  product.CargoPrice, product.AddtCargoPrice, product.DeliveryTime, product.DeliveryPeriod, product.VarrantyTime,
                                                  product.VarrantyPeriod, product.FreeCargoInCity, product.FreeInstallmentInCity, StokTut, product.Transport }
                                                  );
            return id;
        }

        #endregion

        #region SaveProductMeta

        public static int SaveProductMeta(ProductMeta pm)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_SaveProductMeta", new object[] { pm.ProductId, pm.WebTitle, pm.Description, pm.Keywords, pm.CanonicalLink });
            return id;
        }

        #endregion

        #region Edit Product Variant

        #region GetVariantListById

        public static IList<Product> GetVariantListById(int productId)
        {
            List<Product> objectList = new List<Product>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_VariantProducts_List", objectList, new object[] { productId });
            return objectList;
        }

        #endregion

        #region GetProductVariantListById

        public static IList<variantProperties> GetProductVariantListById(int productId)
        {
            List<variantProperties> objectList = new List<variantProperties>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_Variant_List", objectList, new object[] { productId });
            return objectList;
        }

        #endregion

        #region SaveSingleVariant

        public static int SaveSingleVariant(int variantid, int sira, string deger, int dataproduct)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Variant_SaveSingle", new object[] { variantid, sira, deger, dataproduct });
            return id;
        }

        #endregion

        #region RemoveSingleVariant

        public static int RemoveSingleVariant(int ProductId, int VariantId)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Variant_RemoveSingle", new object[] { ProductId, VariantId });
            return id;
        }

        #endregion

        #region InsertSingleVariant

        public static int InsertSingleVariant(int ProductId, string Deger)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Variant_InsertSingle", new object[] { ProductId, Deger });
            return id;
        }

        #endregion

        #region UpdateSingleVariant

        public static int UpdateSingleVariant(int ProductId, int UstUrunId)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Variant_Update_UstId", new object[] { ProductId, UstUrunId });
            return id;
        }

        #endregion

        #region ProductVariantInsertFromMain

        public static int ProductVariantInsertFromMain(int MainProductId, int SubProductId)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Variant_MainToSub", new object[] { MainProductId, SubProductId });
            return id;
        }

        #endregion

        #endregion

        #region ProductGroupList

        public static IList<ProductGroups> ProductGroupList(int ProductId)
        {
            IList<ProductGroups> objectList = new List<ProductGroups>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_Get_GroupList", objectList, new object[] { ProductId });

            return objectList;
        }

        #endregion

        #region GetGroupLiveSearch

        public static IList<ProductGroups> GetGroupLiveSearch(string search)
        {
            IList<ProductGroups> objectList = new List<ProductGroups>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_Get_GroupList_Search", objectList, new object[] { search });

            return objectList;
        }

        #endregion

        #region AddProductToCategory

        public static int AddProductToCategory(int ProductId, int CategoryId, string Gruptip)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_AddToCategory",
                                                  new object[] { ProductId, CategoryId, Gruptip });

            return id;
        }

        #endregion

        #region AddCategoryToProduct

        public static int AddCategoryToProduct(int ProductId, int CategoryId)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_AddToCategory2",
                                                  new object[] { ProductId, CategoryId });

            return id;
        }

        #endregion

        #region RemoveFromCategory

        public static int RemoveFromCategory(int CategoryId, int ProductId)
        {
            int id = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_RemoveFromCategory",
                                                  new object[] { CategoryId, ProductId });

            return id;
        }

        #endregion

        #region SetVariantOptionsFromMain

        public static int SetVariantOptionsFromMain(int id)
        {
            int result = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_SetVariantOptionsFromMain", new object[] { id });
            return result;
        }

        #endregion

        #region ProductActiveToogle

        public static string ProductActiveToogle(int id, short changeAll)
        {
            string result = (string)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Toogle_Active", new object[] { id, changeAll });
            return result;
        }

        #endregion

        #region GetStockAmounts

        public static IList<StockAmount> GetStockAmounts(int ProductId)
        {

            IList<StockAmount> objectList = new List<StockAmount>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_Get_StokMiktar", objectList, new object[] { ProductId });

            return objectList;
        }

        #endregion

        #region GetStockXmlImport

        public static IList<StockXmlImport> GetStockXmlImport(int ProductId)
        {

            IList<StockXmlImport> objectList = new List<StockXmlImport>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_Get_StokXmlImport", objectList, new object[] { ProductId });

            return objectList;
        }

        #endregion

        #region UpdateXmlImportField

        public static int UpdateXmlImportField(string Field, string Value, string ProductId, int XmlId, string Original)
        {
            int result = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Set_StokXmlImportField", new object[] { Field, Value, ProductId, XmlId, Original });
            return result;
        }

        #endregion

        #region DeleteProduct

        public static int DeleteProduct(int ProductId)
        {
            int result = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Delete", new object[] { ProductId });
            return result;
        }

        #endregion

        #region ProductPrices

        public static StockPrices GetStockPrices(int ProductId)
        {
            IList<StockPrices> objectList = new List<StockPrices>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_Get_Prices", objectList, new object[] { ProductId });

            return objectList.FirstOrDefault();
        }

        public static int SetStockPrices(int ProductId, StockPrices Prices)
        {
            int result = (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Set_Prices", new object[] 
            { ProductId, Prices.AlisFiyati, Prices.SatisFiyati, Prices.AlisFiyatiDoviz, Prices.SatisFiyatiDoviz,
            Prices.AlisDovizCinsi, Prices.SatisDovizCinsi, Prices.BayiSatisFiyati, Prices.MinSatisFiyati, 
            Prices.TedarikciFiyati, Prices.StokFiyati });
            return result;
        }


        #endregion

        #region LinkedProducts

        public static IList<LinkedProduct> GetLinkedProducts(int ProductId)
        {
            IList<LinkedProduct> objectList = new List<LinkedProduct>();
            SqlHelper.FillList(dbParameters.dsnStore, "SPR_ESTORE_Product_Linked_List", objectList, new object[] { ProductId });
            return objectList;
        }

        public static int SetLinkedProduct(int Id, int KrtId, int LinkedId, short ListOrder)
        {
            return (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Linked_Set", new object[] { Id, KrtId, LinkedId, ListOrder });
        }

        public static int DeleteLinkedProduct(int Id)
        {
            return (int)SqlHelper.ExecuteScalar(dbParameters.dsnStore, "SPR_ESTORE_Product_Linked_Delete", new object[] { Id });
        }

        #endregion

    }
}
